<?

class tree {

    var $table_default_fields = array('tree.tree_id', 'tree.type_id', 'tree.lineage_base', 'tree.name', 'tree.description', 'tree.main_photo', 'tree.last_updated');
    var $table_destination_fields = array('tree.airport_code_search','tree.airport_codes_search_list','tree.airport_codes_related_list','tree.map_image_url','tree.yahoo_weather_location_id','tree.longtitude','tree.latitude','tree.show_google_map','tree.notes', 'you.youtravel_id','low.country_id as countryID','low.region_id AS regionID','low.resort_id AS resortID', 'parent.name AS parent_name');
    var $table_hotel_fields = array('tree.yahoo_weather_location_id','tree.longtitude','tree.latitude','tree.show_google_map','tree.hotel_rating','tree.review_rating','tree.website_url','tree.notes', 'you.youtravel_id','low.country_id as countryID','low.region_id AS regionID','low.resort_id AS resortID');

    var $table_default_update_fields = array('tree_id', 'type_id', 'lineage_base', 'name', 'description', 'main_photo', 'last_updated');
    var $table_destination_update_fields = array('airport_code_search','airport_codes_search_list','airport_codes_related_list','map_image_url','yahoo_weather_location_id','longtitude','latitude','show_google_map','notes');
    var $table_hotel_update_fields = array('yahoo_weather_location_id','longtitude','latitude','show_google_map','hotel_rating','review_rating','website_url','notes');
 
    
function switch_table_fields($type=''){
	
	switch ($type){
		case 1;
                return array_merge($this->table_destination_fields, $this->table_default_fields);
		break;
		case 2;
                return array_merge($this->table_destination_fields, $this->table_default_fields);
		break;
		case 3;
                return array_merge($this->table_hotel_fields, $this->table_default_fields);
		break;
		default;
			return $this->table_default_fields;
		break;
	}
	
}


function switch_table_update_fields($type=''){
	
	switch ($type){
		case 1;
                return array_merge($this->table_destination_update_fields, $this->table_default_update_fields);
		break;
		case 2;
                return array_merge($this->table_destination_update_fields, $this->table_default_update_fields);
		break;
		case 3;
                return array_merge($this->table_hotel_update_fields, $this->table_default_update_fields);
		break;
		default;
			return $this->table_default_update_fields;
		break;
	}
	
}



// FUNCTION // get single tree item
function get_branch($params, $type=''){
	
	$params = in($params);
        if (!empty($params)) {
            if (isset($params['tree_id'])) {$sql_where .= " AND tree.tree_id = '".$params['tree_id']."' ";}
            if (isset($params['parent_id'])) {$sql_where .= " AND SUBSTRING_INDEX(tree.lineage_base, ',', -1) = '".$params['parent_id']."' ";}
            if (isset($params['type_id'])) {$sql_where .= " AND tree.type_id = '".$params['type_id']."' ";}
            if (isset($params['airport_code'])) {$sql_where .= " AND tree.airport_code_search = '".$params['airport_code']."' ";}
            if (isset($params['child_of'])) {$sql_where .= " AND FIND_IN_SET('".$params['child_of']."', tree.lineage_base) ";}
            if (isset($params['depth'])) {$sql_where .= " AND (LENGTH( tree.lineage_base ) - LENGTH( REPLACE( tree.lineage_base,  ',',  '' ) )) = '".$params['depth']."' ";}
            $sql = "SELECT
		LENGTH( tree.lineage_base ) - LENGTH( REPLACE( tree.lineage_base,  ',',  '' ) ) as depth, 
                SUBSTRING_INDEX(tree.lineage_base, ',', -1) AS parent_id, 
                CONCAT(tree.lineage_base,',',tree.tree_id) AS lineage, 
		".implode(',', $this->switch_table_fields($type))."
		FROM tree AS tree
                LEFT JOIN tree AS parent ON parent.tree_id = tree.parent_id
                LEFT JOIN supplier_locations_you AS you ON you.tree_id=tree.tree_id
                LEFT JOIN supplier_locations_low AS low ON low.tree_id=tree.tree_id
		WHERE 1 = 1
		".$sql_where."
		LIMIT 1";

		return apply_query($sql, $num_rows, $params['cache']);
		
	}

}





// FUNCTION // get number of branch items
function branch_count($params){

	if(isset($params['tree_id'])) $sql_where .= " AND tree_id = '".$params['tree_id']."' ";
	if(isset($params['parent_id'])) $sql_where .= " AND SUBSTRING_INDEX(lineage_base, ',', -1) = '".$params['parent_id']."' ";
	if(isset($params['type_id'])) $sql_where .= " AND type_id = '".$params['type_id']."' ";
	if(isset($params['airport_code'])) $sql_where .= " AND airport_code_search = '".$params['airport_code']."' ";
	if(isset($params['child_of'])) $sql_where .= " AND FIND_IN_SET('".$params['child_of']."', CONCAT(lineage_base,',',tree_id)) ";
	if(isset($params['depth'])) $sql_where .= " AND (LENGTH( lineage_base ) - LENGTH( REPLACE( lineage_base,  ',',  '' ) )) = '".$params['depth']."' ";

	$sql = "SELECT COUNT(tree_id) AS count 
	FROM tree
	WHERE 1 = 1
	".$sql_where." ";
	
	return apply_query($sql);

}





// FUNCTION // search for a destination based on the airport code
function destination_lookup($params){


	$params = in($params);

	if($params['airport_code']) $sql_where .= " AND FIND_IN_SET('".$params['airport_code']."',airport_codes_related_list) ";
	if($params['type_id']) $sql_where .= " AND type_id = '".$params['type_id']."' ";
	if($params['position']){
		switch($params['position']){
			case "top";
				$sql_order .= " ORDER BY (LENGTH( lineage_base ) - LENGTH( REPLACE( lineage_base,  ',',  '' ) )) ASC ";
			break;
			case "bottom";
				$sql_order .= " ORDER BY (LENGTH( lineage_base ) - LENGTH( REPLACE( lineage_base,  ',',  '' ) )) DESC ";
			break;
		}
	}
	

	$sql = "SELECT name, description, airport_code_search, airport_codes_search_list, airport_codes_related_list, map_image_url, tree_id, yahoo_weather_location_id, longtitude, latitude, show_google_map, type_id, SUBSTRING_INDEX(lineage_base, ',', -1) AS parent_id, lineage_base, LENGTH( lineage_base ) - LENGTH( REPLACE( lineage_base,  ',',  '' ) ) AS depth,  CONCAT(lineage_base,',',tree_id) AS lineage, hotel_rating
	FROM tree
	WHERE 1 = 1
	".$sql_where."
	".$sql_order."
	LIMIT 1 ";

	return apply_query($sql);

}




// FUNCTION // Get all children
function get_children($params){

	if($params['parent_id']) $where .= " AND FIND_IN_SET('".$params['parent_id']."',tree.lineage_base) ";
	if($params['type_id']) $where .= " AND tree.type_id = '".$params['type_id']."' ";
	if($params['type_id_exlude']) $where .= " AND tree.type_id != '".$params['type_id_exlude']."' ";        

	$sql = "SELECT tree.tree_id, tree.type_id, tree.lineage_base, tree.parent_id,  tree.name, tree.description, tree.main_photo, tree.last_updated
	FROM tree
	WHERE 1 = 1
	".$where."
        ORDER BY tree.name "; 
        
	return apply_query($sql, $num_rows, $params['cache']);

}


// FUNCTION // get the pratent tree id
function get_parent_tree_id($lineage_base){
	
	return substr(strrchr($lineage_base,","),1);

}


// FUNCTION // insert destination details
function insert_branch($params,$type){

	$params = array_remove_empty($params);
	$params = pair_array_data($params, $this->switch_table_update_fields($type));

	$params = in($params);

	$sql = "INSERT INTO tree
	SET	".make_set_clause($params).", last_updated = '".mktime()."' ";

	$result = mysql_query($sql) or die (mysql_error());
	
	return mysql_insert_id();
	
}

// FUNCTION // update destination details
function update_branch($params,$type){

	
	$params = array_remove_empty($params);
        
	$params = pair_array_data($params, $this->switch_table_update_fields($type));
        
	$params = in($params);
	$tree_id = $params['tree_id'];
	unset($params['tree_id']);

	$sql = "UPDATE tree
	SET ".make_set_clause($params).", last_updated = '".mktime()."'
	WHERE tree_id = '".$tree_id."' LIMIT 1";

	
	$result = mysql_query($sql) or die (mysql_error());
	
}




// FUNCTION // Delete tree branch
function delete_branch($params){

	if($params['tree_id']) $sql_where .= " AND tree_id = '".$params['tree_id']."' ";
	if($params['type_id']) $sql_where .= " AND type_id = '".$params['type_id']."' ";
	
	if(isset($sql_where)){
	
		$sql = "DELETE
		FROM tree
		WHERE 1 = 1
		".$sql_where."
		LIMIT 1 ";
		
		$result = mysql_query($sql) or die (mysql_error());
		
	}

}




function photo_url($main_photo){
	
	if(!isset($main_photo)){
		$main_photo = "photo_thumb_no_photo.gif";
	}
	
	return UPLOAD_HTTP_PERM.$main_photo;
	
}


function tree_breakdown($params){
	
	
	$sql = "SELECT T.name, T.tree_id,
	(
	SELECT COUNT(U.name)
	FROM tree AS U
	WHERE U.type_id = 1
	AND FIND_IN_SET(T.tree_id, U.lineage_base)) AS count_destinations,
	(
	SELECT COUNT(R.name)
	FROM tree AS R
	WHERE R.type_id = 2
	AND FIND_IN_SET(T.tree_id, R.lineage_base)) AS count_resorts,
	(
	SELECT COUNT(S.name)
	FROM tree AS S
	WHERE S.type_id = 3
	AND FIND_IN_SET(T.tree_id, S.lineage_base)) AS count_hotels
	FROM tree AS T
	WHERE T.type_id = 1";

        return apply_query($sql);
            
    }
    
 //" AND FIND_IN_SET('".$params['parent_id']."',CONCAT(lineage_base,',',tree_id)) ".    
 // FUNCTION // get single tree item
    
    
    function get_branch_by_parent($params, $type=''){  
        $params = in($params);
        if (!empty($params)) {
            if (isset($params['parent_id'])) {

                $sql_where .= " AND FIND_IN_SET('". $params['parent_id'] ."',CONCAT(tree.lineage_base,',',tree.tree_id)) 
                                AND LOW.RESORT LIKE (SELECT NAME FROM TREE TR WHERE TR.tree_id = " . $params['tree_id'] ." )";
                   
            }
            if (isset($params['type_id'])) {$sql_where .= " AND type_id = '".$params['type_id']."' ";}
            if (isset($params['airport_code'])) {$sql_where .= " AND airport_code_search = '".$params['airport_code']."' ";}
            if (isset($params['child_of'])) {$sql_where .= " AND FIND_IN_SET('".$params['child_of']."', lineage_base) ";}
            if (isset($params['depth'])) {$sql_where .= " AND (LENGTH( lineage_base ) - LENGTH( REPLACE( lineage_base,  ',',  '' ) )) = '".$params['depth']."' ";}
            $sql = "SELECT
                LENGTH( lineage_base ) - LENGTH( REPLACE( lineage_base,  ',',  '' ) ) as depth, 
                SUBSTRING_INDEX(lineage_base, ',', -1) AS parent_id, 
                CONCAT(lineage_base,',',tree.tree_id) AS lineage, 
                ".implode(',', $this->switch_table_fields($type))."
            FROM tree
            LEFT JOIN supplier_locations_you AS you ON you.tree_id = tree.tree_id
            LEFT JOIN supplier_locations_low AS low ON low.tree_id = tree.tree_id
            WHERE 1 = 1
            ".$sql_where."
            ORDER BY tree.name LIMIT 1";

            return apply_query($sql);
            
    }
    }
    
    
    
    function get_resorts_by_destination($parent_id){
        
        if ($parent_id) {

            $sql = "select t.*
                    from 
                      tree as t
                      join supplier_locations_low l ON t.tree_id = l.tree_id
                    where 
                       t.type_id = '2' AND FIND_IN_SET('".$parent_id."',t.lineage_base)
                    ORDER BY t.name"; 
            
            return apply_query($sql, $num_rows);                    
            
        } else return null;
        

        

        
    }
    
    
    
        
}
